package cn.edu.sziit.dao;

import java.sql.Connection;
import java.sql.ResultSet;

import cn.edu.sziit.model.Grade;
import cn.edu.sziit.model.PageBean;
import cn.edu.sziit.util.StringUtil;
import com.mysql.jdbc.PreparedStatement;

public class GradeDao {

	public ResultSet gradeList(Connection con, PageBean pageBean, Grade grade) throws Exception {
		/*
		 * StringBuffer sb=new StringBuffer("select * from t_grade ");
		 * if(pageBean!=null){
		 * sb.append("limit"+pageBean.getStart()+","+pageBean.getRows()); }
		 * PreparedStatement pstmt=(PreparedStatement)
		 * con.prepareStatement(sb.toString());
		 * 
		 * return pstmt.executeQuery();
		 */
		StringBuffer sb = new StringBuffer("select * from t_grade");
		if (grade!=null&&StringUtil.isNotEmpty(grade.getGradeName())) {
			sb.append(" and gradeName like '%" + grade.getGradeName() + "%'");
		}
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
		}
		PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		return pstmt.executeQuery();

	}

	/**
	 * 总记录数的方法
	 * 
	 * @param con
	 * @return
	 * @throws Exception
	 */
	/*
	 * public int gradeCount (Connection con)throws Exception{ String
	 * sql="select count(*) as total from t_grade"; PreparedStatement
	 * pstmt=(PreparedStatement) con.prepareStatement(sql); ResultSet
	 * rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt("total"); }else{
	 * return 0; } }
	 */
	public int gradeCount(Connection con, Grade grade) throws Exception {
		StringBuffer sb = new StringBuffer("select count(*) as total from t_grade");
		if (StringUtil.isNotEmpty(grade.getGradeName())) {
			sb.append(" and gradeName like '%" + grade.getGradeName() + "%'");
		}
		PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		} else {
			return 0;
		}
	}

	public int gradeDelete(Connection con, String delIds) throws Exception {
		String sql = "delete from t_grade where id in(" + delIds + ")";
		PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
		return pstmt.executeUpdate();

	}
   public int gradeAdd(Connection con,Grade grade) throws Exception{
	   String sql="insert into t_grade values(null,?,?,?)";
	   PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
	   pstmt.setString(1, grade.getGradeName());
	   pstmt.setString(2, grade.getGradeDese());
	   pstmt.setString(3,grade.getGradeTeacher());
	   return pstmt.executeUpdate();
	   
   }
   public int gradeModify(Connection con,Grade grade)throws Exception{
	   String sql="update t_grade set gradeName=?,gradeDese=?,gradeTeacher=? where id=?";
	   PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
	   pstmt.setString(1, grade.getGradeName());
	   pstmt.setString(2, grade.getGradeDese());
	   pstmt.setString(3,grade.getGradeTeacher());
	   pstmt.setInt(4, grade.getId());
	   
	   return pstmt.executeUpdate();
	   
   }
}
